﻿/***************************************************************
 * 开发人员：网魂小兵@http://www.mozlite.com
 * 
 * MySQL通用数据库提供者。
 * 
 * 最后修改：2011-10-31
 * ************************************************************/
namespace Mozlite.Data.MySqlDataProvider
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Mozlite.Common;
    using MySql.Data.MySqlClient;
    using Mozlite.Collections;

    /// <summary>
    /// 通用数据库提供者。
    /// </summary>
    public class MySqlCommonDataProvider : CommonDataProvider
    {
        /// <summary>
        /// 初始化类<see cref="T:Mozlite.Data.MySqlDataProvider.MySqlCommonDataProvider"/>。
        /// </summary>
        public MySqlCommonDataProvider() : base(MySqlClientFactory.Instance) { }

        #region logs
        public override bool DeleteLogs(int settingsID, string logIDs)
        {
            if (settingsID > 0)
                return ExecuteNonQuery("DELETE FROM {0} WHERE SettingsID = {1} AND LogID in ({2});", AddProfix("Logs"), settingsID, Q(logIDs));
            return ExecuteNonQuery("DELETE FROM {0} WHERE LogID in ({1});", AddProfix("Logs"), Q(logIDs));
        }

        public override void ClearLogs(int settingsID, DateTime date)
        {
            if (settingsID > 0)
                ExecuteNonQuery("DELETE FROM {0} WHERE SettingsID = {1} AND LogDate <= '{2}';", AddProfix("Logs"), settingsID, Q(date));
            else
                ExecuteNonQuery("DELETE FROM {0} WHERE LogDate <= '{1}';", AddProfix("Logs"), Q(date));
        }

        public override void WriteLog(LogInfo info)
        {
            ExecuteNonQuery("REPLACE INTO {0}(SettingsID, `Message`,`Category`,`EventID`,`Source`,LogDate,MachineName,`LogType`)VALUES('{1}','{2}','{3}','{4}','{5}',now(),'{6}','{7}');", AddProfix("Logs"), info.SettingsID, Q(info.Message), Q(info.Category), Q(info.EventID), Q(info.Source), Q(info.MachineName), Q(info.LogType));
        }

        public override List<LogInfo> LoadLogs(LogQuery query, out int totalSize)
        {
            string sql = string.Format("SELECT LogID,`Category`,`EventID`,`Source`,LogDate,MachineName,`LogType` FROM {0} ", AddProfix("Logs"));
            string count = string.Format("SELECT COUNT(*) FROM {0} ", AddProfix("Logs"));
            List<string> wheres = new List<string>();
            if (query.SettingsID > 0)
                wheres.Add("SettingsID = " + query.SettingsID);
            if (query.EventID != null)
                wheres.Add("EventID = '" + Q(query.EventID) + "'");
            if (query.LogType != null)
                wheres.Add("LogType = '" + Q(query.LogType) + "'");
            if (wheres.Count > 0)
            {
                sql += "WHERE " + wheres.Join(" AND ") + " ";
                count += "WHERE " + wheres.Join(" AND ");
            }
            sql += query.ToOrderBySQL() + "LIMIT " + (query.PageIndex * query.PageSize) + "," + query.PageSize + ";\r\n"; ;
            count += ";";
            return ExecuteReader(sql + count).TGetList<LogInfo>(out totalSize);
        }

        public override LogInfo GetLog(int logID)
        {
            return ExecuteReader("SELECT * FROM {0} WHERE LogID = '{1}';", AddProfix("Logs"), logID).TGet<LogInfo>();
        }
        #endregion

        #region mails
        public override void DeleteQueuedMail(Guid emailID)
        {
            throw new NotImplementedException();
        }

        public override Dictionary<Guid, Mail> DequeueMails(int messageLimit)
        {
            throw new NotImplementedException();
        }

        public override void QueueMail(Mail message)
        {
            throw new NotImplementedException();
        }

        public override void QueueSendingFailure(List<Guid> list, int failureInterval, int maxNumberOfTries)
        {
            throw new NotImplementedException();
        }
        #endregion

        #region applications
        public override List<string> LoadAreas()
        {
            List<string> list = new List<string>();
            using (var reader = ExecuteReader("SELECT DISTINCT AreaName FROM {0};", AddProfix("Applications")))
            {
                while (reader.Read())
                {
                    list.Add(reader.GetString("AreaName"));
                }
                reader.Close();
            }
            return list;
        }

        public override bool SaveApplication<TApplication, TConfiguration>(TApplication application)
        {
            if (ExecuteScalar<bool>("SELECT 1 FROM {0} WHERE ApplicationID = '{1}';", AddProfix("Applications"), Q(application.ApplicationID)))
                return ExecuteNonQuery("UPDATE {0} SET AreaName = '{1}', ApplicationName = '{2}', `VersionBuild` = '{3}', UpgradeUrl = '{4}', `Type` = '{5}', ApplicationData = '{7}' WHERE ApplicationID = '{6}';", AddProfix("Applications"),
                    Q(application.AreaName), Q(application.ApplicationName), application.VersionBuild, Q(application.UpgradeUrl), Q(application.GetInstanceType()), Q(application.ApplicationID), Q(application.ToString()));
            return ExecuteNonQuery("INSERT INTO {0} (ApplicationId, AreaName, ApplicationName, `VersionBuild`, UpgradeUrl, `Type`, CreateDate, ApplicationData)VALUES('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}');", AddProfix("Applications"),
                Q(application.ApplicationID), Q(application.AreaName), Q(application.ApplicationName), application.VersionBuild, Q(application.UpgradeUrl), Q(application.GetInstanceType()), Q(DateTime.Now), Q(application.ToString()));
        }

        public override bool DeleteApplication(string applicationID)
        {
            var transaction = GetTransaction();
            try
            {
                ExecuteNonQuery(transaction, string.Format("DELETE FROM {0} WHERE ApplicationID = '{1}'", AddProfix("Configuration"), Q(applicationID)));
                ExecuteNonQuery(transaction, string.Format("DELETE FROM {0} WHERE ApplicationID = '{1}'", AddProfix("Applications"), Q(applicationID)));
                transaction.CommitTransaction();
                return true;
            }
            catch
            {
                transaction.RollbackTransaction();
                return false;
            }
        }

        public override Dictionary<string, ApplicationBase> LoadApplications()
        {
            Dictionary<string, ApplicationBase> list = new Dictionary<string, ApplicationBase>(StringComparer.OrdinalIgnoreCase);
            using (var reader = ExecuteReader("SELECT * FROM {0};", AddProfix("Applications")))
            {
                while (reader.Read())
                {
                    try
                    {
                        var application = ApplicationBase.CreateInstance(reader);
                        if (application != null)
                            list.Add(application.ApplicationID, application);
                    }
                    catch (Exception ex) { Logs.Error("应用程序实例化错误：", "应用程序", EventID.Database, ex); }
                }
                reader.Close();
            }
            return list;
        }

        public override TApplication GetApplication<TApplication, TConfiguration>(string applicationID)
        {
            return ExecuteReader("SELECT * FROM {0} WHERE ApplicationID = '{1}'", AddProfix("Applications"), Q(applicationID)).TGet<TApplication>();
        }
        
        public override bool SaveConfiguration<TConfiguration>(TConfiguration configuration)
        {
            return ExecuteNonQuery("REPLACE INTO {0}(ApplicationID,SettingsID,ConfigurationData,LastModify,`Type`)VALUES('{1}', '{2}', '{3}', now(),'{4}');", AddProfix("Configuration"), Q(configuration.ApplicationID), configuration.SettingsID, Q(configuration.ToString()), Q(configuration.GetInstanceType()));
        }

        public override ConfigurationBase GetConfiguration(string applicationID, int settingsID)
        {
            ConfigurationBase configuration = null;
            using (var reader = ExecuteReader("SELECT * FROM {0} WHERE ApplicationID = '{1}' AND SettingsID = '{2}';", AddProfix("Configuration"), Q(applicationID), settingsID)) {
                if (reader.Read())
                    configuration = ConfigurationBase.CreateInstance(reader);
                reader.Close();
            }
            return configuration;
        }
        #endregion

        #region categories
        public override bool DeleteCategory(int categoryID, int settingsID)
        {
            return ExecuteNonQuery("DELETE FROM {0} WHERE CategoryID = {1} AND SettingsID = {2};", AddProfix("Categories"), categoryID, settingsID);
        }

        public override CreateCategoryStatus SaveCategory(Category category)
        {
            if (category.CategoryID > 0)
            {
                if (ExecuteNonQuery("UPDATE {0} SET `ParentID` = '{3}',`ApplicationType` = '{4}',`Key` = '{5}',`CategoryName` = '{6}',`Description` = '{7}',`LogoUrl` = '{8}', `Extensions` = '{9}' WHERE `SettingsID` = '{1}' AND `CategoryID` = '{2}';", AddProfix("Categories"), Q(category.SettingsID), Q(category.CategoryID), Q(category.ParentID), Q(category.ApplicationType), Q(category.Key), Q(category.CategoryName), Q(category.Description), Q(category.LogoUrl), Q(category.ToString())))
                    return CreateCategoryStatus.Updated;
            }
            else if(ExecuteNonQuery("INSERT INTO {0}(`SettingsID`,`ParentID`,`ApplicationType`,`Key`,`CategoryName`,`Description`,`LogoUrl`,`CreateDate`,`Threads`,`Extensions`)VALUES('{1}','{2}','{3}','{4}','{5}','{6}','{7}',now(),'{8}','{9}');", AddProfix("Categories"),Q(category.SettingsID),Q(category.ParentID),Q(category.ApplicationType),Q(category.Key),Q(category.CategoryName),Q(category.Description),Q(category.LogoUrl),Q(category.Threads),Q(category.ToString())))
                return CreateCategoryStatus.Created;
            return CreateCategoryStatus.UnknownError;
        }

        public override Dictionary<int, Category> LoadCategories(int settingsID)
        {
            var categories = new Dictionary<int, Category>();
            using (var reader = ExecuteReader("SELECT * FROM {0} WHERE SettingsID = {1};", AddProfix("Categories"), settingsID))
            {
                while (reader.Read())
                {
                    var category = new Category();
                    category.LoadFromDataReader(reader);
                    categories.Add(category.CategoryID, category);
                }
                reader.Close();
            }
            return categories;
        }

        public override Category GetCategory(int categoryID, int settingsID)
        {
            return ExecuteReader("SELECT * FROM {0} WHERE SettingsID = {1} AND CategoryID = {2};", AddProfix("Categories"), settingsID, categoryID).TGet<Category>();
        }

        public override void CalculateThreadsCount()
        {
            ExecuteNonQuery("UPDATE {0} SET Threads = (SELECT COUNT(1) FROM {1} WHERE {0}.CategoryID = {1}.CategoryID AND {0}.SettingsID = {1}.SettingsID AND {1}.IsApproved = 1);", AddProfix("Categories"), AddProfix("Threads"));
            ExecuteNonQuery("UPDATE {0} SET PostCount = (SELECT COUNT(1) FROM {1} WHERE {0}.ThreadID = {1}.ThreadID AND {0}.SettingsID = {1}.SettingsID AND {1}.IsApproved = 1);", AddProfix("Threads"), AddProfix("Posts"));
        }
        #endregion

        #region threads
        public override CreateThreadStatus SaveThread(Thread thread)
        {
            if (!string.IsNullOrEmpty(thread.Key)&&ExecuteScalar<bool>("SELECT 1 FROM {0} WHERE SettingsID = '{1}' AND `Key` = '{2}' AND ThreadID <> '{3}';", AddProfix("Threads"), thread.SettingsID, Q(thread.Key), thread.ThreadID))
                return CreateThreadStatus.DuplicateKey;
            var transaction = GetTransaction();
            if (thread.ThreadID > 0)
            {
                try
                {
                    ExecuteNonQuery(transaction, string.Format("DELETE FROM {0} WHERE SettingsID = {1} AND ThreadID = {2}", AddProfix("ThreadsTags"), thread.SettingsID, thread.ThreadID));

                    if (!string.IsNullOrEmpty(thread.Tags))
                    {
                        var sql = new List<string>();
                        foreach (var tag in thread.Tags.Split<string>())
                        {
                            sql.Add(string.Format("INSERT IGNORE INTO {0}(SettingsID, TagName,`Count`)VALUES('{1}', '{2}', 0);", AddProfix("Tags"), thread.SettingsID, Q(tag)));
                        }
                        ExecuteNonQuery(transaction, sql.Join("\r\n"));

                        ExecuteNonQuery(transaction, string.Format("INSERT INTO {0}(SettingsID, UserID, TagID, ThreadID) SELECT {1}, {5}, TagID, {2} FROM {3} WHERE TagName IN({4});", AddProfix("ThreadsTags"), thread.SettingsID, thread.ThreadID, AddProfix("Tags"), thread.Tags.Split<string>().Join(", ", tag => { return string.Format("'{0}'", Q(tag)); }), thread.UserID));
                    }

                    ExecuteNonQuery("UPDATE {0} SET `CategoryID` = '{1}',`Tags` = '{2}',`Title` = '{3}',`Summary` = '{4}',`Body` = '{5}',`Key` = '{6}',`LastModify` = now(),`ModeratorID` = '{7}',`LastModeratorDate` = now(),`IsApproved` = '{8}',`AllowedApply` = '{9}',`AllowedAnonymousApply` = '{10}',`LogoUrl` = '{11}',`IsStrict` = '{12}',`SortOrder` = '{13}', Extensions = '{16}' WHERE `SettingsID` = '{14}' AND `ThreadID` = '{15}';", AddProfix("Threads"), Q(thread.CategoryID), Q(thread.Tags), Q(thread.Title), Q(thread.Summary), Q(thread.Body), Q(thread.Key), Q(thread.ModeratorID), Q(thread.IsApproved), Q(thread.AllowedApply), Q(thread.AllowedAnonymousApply), Q(thread.LogoUrl), Q(thread.IsStrict), Q(thread.SortOrder), thread.SettingsID, thread.ThreadID, Q(thread.ToString()));

                    transaction.CommitTransaction();
                    return CreateThreadStatus.Updated;
                }
                catch { transaction.RollbackTransaction(); }
            }
            else
            {
                if (!string.IsNullOrEmpty(thread.Tags))
                {
                    var sql = new List<string>();
                    foreach (var tag in thread.Tags.Split<string>())
                    {
                        sql.Add(string.Format("INSERT IGNORE INTO {0}(SettingsID, TagName,`Count`)VALUES('{1}', '{2}', 0);", AddProfix("Tags"), thread.SettingsID, Q(tag)));
                    }
                    ExecuteNonQuery(transaction, sql.Join("\r\n"));

                    ExecuteNonQuery(transaction, string.Format("INSERT INTO {0}(SettingsID, UserID, TagID, ThreadID) SELECT {1}, {5}, TagID, {2} FROM {3} WHERE TagName IN({4});", AddProfix("ThreadsTags"), thread.SettingsID, thread.ThreadID, AddProfix("Tags"), thread.Tags.Split<string>().Join(", ", tag => { return string.Format("'{0}'", Q(tag)); }), thread.UserID));
                }

                ExecuteNonQuery(transaction, string.Format("INSERT INTO {0}(`SettingsID`,`CategoryID`,`Tags`,`Title`,`Summary`,`Body`,`Key`,`CreateDate`,`LastModify`,`UserID`,`IsApproved`,`AllowedApply`,`AllowedAnonymousApply`,`UserIP`,`LogoUrl`,`IsStrict`,`SortOrder`,PostCount,Extensions)VALUES('{1}','{2}','{3}','{4}','{5}','{6}','{7}', now(), now(),'{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',0,'{16}');", AddProfix("Threads"), Q(thread.SettingsID), Q(thread.CategoryID), Q(thread.Tags), Q(thread.Title), Q(thread.Summary), Q(thread.Body), Q(thread.Key), Q(thread.UserID), Q(thread.IsApproved), Q(thread.AllowedApply), Q(thread.AllowedAnonymousApply), Q(thread.UserIP), Q(thread.LogoUrl), Q(thread.IsStrict), Q(thread.SortOrder), Q(thread.ToString())));

                transaction.CommitTransaction();
                return CreateThreadStatus.Created;
            }
            return CreateThreadStatus.UnknownError;
        }

        private string selectedFields = "ThreadID, SettingsID, CategoryID, UserID, `Title`, Tags, Summary, PostCount, CreateDate, LastModify, IsApproved, LogoUrl, `Key`";

        public override List<Thread> GetTreadByTag(int pageIndex, int pageSize, int settingsID, string tagName, out int size)
        {
            string sqlFormat = "SELECT {0} " + string.Format("FROM {0} thread INNER JOIN {1} tag ON thread.ThreadID = tag.ThreadID AND tag.SettingsID = {3} INNER JOIN {2} tt ON tt.ThreadID = thread.ThreadID AND tt.TagID = tag.TagID AND tt.SettingsID = {3} WHERE tag.TagName = '{4}' AND thread.SettingsID = {3}", AddProfix("Threads"), AddProfix("Tags"), AddProfix("ThreadsTags"), settingsID, tagName);
            string sql = string.Format(sqlFormat, selectedFields);
            sql += " ORDER BY ThreadID DESC LIMIT " + (pageIndex * pageSize) + "," + pageSize + ";\r\n";
            sql += string.Format(sqlFormat, "COUNT(*)");
            sql += ";";
            return ExecuteReader(sql).TGetList<Thread>(out size);
        }

        public override List<Thread> GetThreads(ThreadQuery query, out int size)
        {
            var wheres = new List<string>();
            wheres.Add("SettingsID = " + query.SettingsID);
            if (query.CategoryID != null)
                wheres.Add("CategoryID IN(" + Categories.GetCategoryIDs(query.CategoryID.Value) + ")");
            if(query.IsApproved != null)
                wheres.Add("IsApproved = " + Q(query.IsApproved.Value));
            if (!string.IsNullOrEmpty(query.Title))
                wheres.Add("`Title` LIKE '%" + Q(query.Title) + "%'");
            if (!string.IsNullOrEmpty(query.Summary))
                wheres.Add("`Summary` LIKE '%" + Q(query.Summary) + "%'");
            if (!string.IsNullOrEmpty(query.Body))
                wheres.Add("`Body` LIKE '%" + Q(query.Body) + "%'");
            if (query.UserID != null)
                wheres.Add("UserID = " + query.UserID);
            return ExecuteReader("SELECT {2} FROM {0} WHERE {1} {3} LIMIT " + (query.PageIndex * query.PageSize) + "," + query.PageSize + ";\r\nSELECT COUNT(*) FROM {0} WHERE {1};", AddProfix("Threads"), wheres.Join(" AND "), selectedFields, query.ToOrderBySQL()).TGetList<Thread>(out size);
        }

        public override Thread GetThread(int threadID, string key, bool addViews, int settingsID)
        {
            List<string> wheres = new List<string>();
            wheres.Add("SettingsID = " + settingsID);
            if (threadID > 0)
                wheres.Add("ThreadID = " + threadID);
            else if (!string.IsNullOrEmpty(key))
                wheres.Add("`Key` = '" + Q(key) + "'");
            else
                return null;

            string where = wheres.Join(" AND ");
            string sql = null;
            if (addViews)
                sql = string.Format("UPDATE {0} SET Views = Views + 1 WHERE {1};", AddProfix("Threads"), where);
            sql += string.Format("SELECT * FROM {0} WHERE {1};", AddProfix("Threads"), where);
            return ExecuteReader(sql).TGet<Thread>();
        }

        public override bool DeleteThreads(int threadID, int settingsID)
        {
            var transaction = GetTransaction();
            try
            {
                ExecuteNonQuery(transaction, string.Format("DELETE FROM {0} WHERE SettingsID = {1} AND ThreadID = {2};", AddProfix("Posts"), settingsID, threadID));
                ExecuteNonQuery(transaction, string.Format("DELETE FROM {0} WHERE SettingsID = {1} AND ThreadID = {2};", AddProfix("ThreadsTags"), settingsID, threadID));
                ExecuteNonQuery(transaction, string.Format("DELETE FROM {0} WHERE SettingsID = {1} AND ThreadID = {2};", AddProfix("Threads"), settingsID, threadID));
                transaction.CommitTransaction();
                return true;
            }
            catch { }
            return false;
        }
        #endregion

        #region tags
        public override bool SaveTag(Tag tag)
        {
            if(tag.TagID > 0)
                return ExecuteNonQuery("UPDATE {0} SET TagName = '{1}' WHERE SettingsID = '{2}' AND TagID = '{3}';", AddProfix("Tags"), Q(tag.TagName), tag.SettingsID, tag.TagID);
            return ExecuteNonQuery("INSERT INTO {0}(SettingsID, UserID, TagName,`Count`)VALUES('{1}', '{2}', '{3}', 0);", AddProfix("Tags"), tag.SettingsID, tag.UserID, Q(tag.TagName));
        }

        public override List<Tag> LoadTags(int settingsID, int userID)
        {
            return ExecuteReader("SELECT * FROM {0} WHERE SettingsID = '{1}' AND UserID = '{2}';", AddProfix("Tags"), settingsID, userID).TGetList<Tag>();
        }

        #endregion

        #region posts
        public override bool SavePost(Post post)
        {
            if (post.PostID > 0)
            {
                return ExecuteNonQuery("UPDATE {0} SET `Body` = '{1}', `LastModified` = now(), `IsApproved` = '{2}',`Extensions` = '{3}' WHERE SettingsID = '{4}' AND PostID = '{5}';", AddProfix("Posts"), Q(post.Body), Q(post.IsApproved), Q(post.ToString()), post.SettingsID, post.PostID);
            }
            else
            {
                var transaction = GetTransaction();
                try
                {
                    post.PostID = ExecuteScalar<int>(transaction, string.Format("INSERT INTO {0}(`SettingsID`,`ThreadID`,`UserID`,`Body`,`CreateDate`,`LastModified`,`UserIP`,`IsApproved`,`Extensions`)VALUES('{1}','{2}','{3}','{4}',now(),now(),'{5}','{6}','{7}'); SELECT last_insert_id();", AddProfix("Posts"), Q(post.SettingsID), Q(post.ThreadID), Q(post.UserID), Q(post.Body), Q(post.UserIP), Q(post.IsApproved), Q(post.ToString())));
                    if (post.PostID > 0)
                    {
                        ExecuteNonQuery("UPDATE {0} SET LastPostID = {1}, LastPostUserID = {2}, LastPostDate = now() WHERE ThreadID = {3} AND SettingsID = {4};", AddProfix("Threads"), post.PostID, post.UserID, post.ThreadID, post.SettingsID);
                        transaction.CommitTransaction();
                        return true;
                    }
                }
                catch { }
                transaction.RollbackTransaction();
                return false;
            }
        }

        public override List<Post> LoadPosts(int pageIndex, int pageSize, int? threadID, int settingsID, int? userid, out int size)
        {
            var wheres = new StringList() ;
            wheres.AddFormat("SettingsID = {0}", settingsID);
            if (threadID != null)
                wheres.AddFormat("ThreadID = {0}", threadID.Value);
            if (userid != null)
                wheres.AddFormat("UserID = {0}", userid.Value);
            return ExecuteReader("SELECT * FROM {0} WHERE {1}; SELECT COUNT(*) FROM {0} WHERE {1};", AddProfix("Posts"), wheres.Join(" AND ")).TGetList<Post>(out size);
        }

        public override bool DeletePost(int postID, int settingsID)
        {
            return ExecuteNonQuery("DELETE FROM {0} WHERE SettingsID = {1} AND PostID = {2};", AddProfix("Posts"), settingsID, postID);
        }
        #endregion

        #region navigators
        public override bool SaveNavigator(Navigator navigator)
        {
            if (navigator.NavigatorID > 0)
                return ExecuteNonQuery("UPDATE {0} SET DisplayName = '{1}', EnglishName = '{2}', ParentID = '{3}', `SortOrder` = '{4}', NavigatorUrl = '{5}', OnClick = '{6}', `Title` = '{7}', `Style` = '{8}', `Key` = '{12}' WHERE ThemeID = '{9}' AND SettingsID = '{10}' AND NavigatorID = '{11}';", AddProfix("Navigators"),
                    Q(navigator.DisplayName), Q(navigator.EnglishName), navigator.ParentID, navigator.SortOrder, Q(navigator.NavigatorUrl), Q(navigator.OnClick), Q(navigator.Title), Q(navigator.Style), Q(navigator.ThemeID), navigator.SettingsID, navigator.NavigatorID, Q(navigator.Key));
            return ExecuteNonQuery("INSERT INTO {0}(ThemeID, SettingsID, DisplayName, EnglishName, ParentID, `SortOrder`, NavigatorUrl, OnClick, `Title`, `Style`, `Key`, `Location`)VALUES('{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}');", AddProfix("Navigators"),
                Q(navigator.ThemeID), navigator.SettingsID, Q(navigator.DisplayName), Q(navigator.EnglishName), navigator.ParentID, navigator.SortOrder, Q(navigator.NavigatorUrl), Q(navigator.OnClick), Q(navigator.Title), Q(navigator.Style), Q(navigator.Key), Q(navigator.Location));
        }

        public override Dictionary<NavigatorLocation, Dictionary<int, Navigator>> LoadNavigators(int settingsID, Guid themeID)
        {
            var navigators = new Dictionary<NavigatorLocation, Dictionary<int, Navigator>>();
            using (var reader = ExecuteReader("SELECT * FROM {0} WHERE SettingsID = '{1}' AND ThemeID = '{2}' ORDER BY SortOrder;", AddProfix("Navigators"), settingsID, Q(themeID)))
            {
                while (reader.Read())
                { 
                    var navigator = new Navigator();
                    navigator.LoadFromDataReader(reader);
                    if (!navigators.ContainsKey(navigator.Location))
                        navigators.Add(navigator.Location, new Dictionary<int, Navigator>());
                    navigators[navigator.Location].Add(navigator.NavigatorID, navigator);
                }
                reader.Close();
            }
            return navigators;
        }

        public override Navigator GetNavigator(int settingsID, int navigatorID)
        {
            return ExecuteReader("SELECT * FROM {0} WHERE SettingsID = '{1}' AND NavigatorID = '{2}';", AddProfix("Navigators"), settingsID, navigatorID).TGet<Navigator>();
        }
        #endregion

        #region contents
        public override bool SaveContent(ContentContainer content)
        {
            throw new NotImplementedException();
        }

        public override ContentContainer GetContent(int contentID, string key, int settingsID, Guid themeID)
        {
            throw new NotImplementedException();
        }

        public override string GetContentBody(string key, int settingsID)
        {
            throw new NotImplementedException();
        }

        public override bool DeleteContent(int contentID, int settingsID)
        {
            throw new NotImplementedException();
        }
        #endregion
    }
}